package powell.nflpredictionproject.database;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 *
 * @author Joshua Powell
 */

/*
 * This class builds the database so that information can be stored into it. 
 * Also, this class contains a DropTables() method that when called checks to
 * see if the database tables exist and if so drop them from the database. If
 * the DropTables() method does not detect the tables existing it does nothing.
 */
public class BuildDatabase
{

    /*
     * This method will drop the tables Stats2012 and Stats2013 if they already
     * ecist and if they do not then it will do nothing.
     */
    public void DropTables()
    {
        System.out.println("Checking for existing tables.");

        try
        {
            Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase;create=true");
            // Get a Statement object.
            Statement stmt = conn.createStatement();;

            try
            {
                /*
                 * This block of code drops the tables Stats2012 and Stats2013
                 * if they already exist.
                 */
                stmt.execute("DROP TABLE Stats2012");
                System.out.println("Stats table dropped.");
                stmt.execute("DROP TABLE Stats2013");
                System.out.println("Stats table dropped.");
            } catch (SQLException ex)
            {
                /*
                 * No need to add anything here.
                 */
            }
        } catch (SQLException ex)
        {
            System.out.println("ERROR: " + ex.getMessage());
            ex.printStackTrace();
        }
    }

    /*
     * This method builds the Stats2012 and Stats2013 tables and creating 
     * columns based on stats read from a .CSV file.
     * 
     * @throws FileNotFoundException, SQLException
     */
    public void BuildStatsTables() throws FileNotFoundException, SQLException
    {
        File inFile = new File("Stats.txt");
        Scanner readFile = new Scanner(inFile);
        String[] statCategories = readFile.nextLine().split(",");


        Connection conn = DriverManager.getConnection("jdbc:derby:NFLStatsDatabase;create=true");
        Statement stmt = conn.createStatement();

        /*
         * Create the Stats2012 Table
         */
        String createTable2012 = "CREATE TABLE Stats2012("
                + statCategories[1] + " CHAR(25) Default Null, "
                + statCategories[2] + " DECIMAL Default Null, "
                + statCategories[3] + " DECIMAL Default Null, "
                + statCategories[4] + " CHAR(25) Default Null, "
                + statCategories[5] + " DECIMAL Default Null, "
                + statCategories[6] + " DECIMAL Default Null, "
                + statCategories[7] + " DECIMAL Default Null, "
                + statCategories[8] + " DECIMAL Default Null, "
                + statCategories[9] + " DECIMAL Default Null, "
                + statCategories[10] + " DECIMAL Default Null, "
                + statCategories[11] + " DECIMAL Default Null, "
                + statCategories[12] + " DECIMAL Default Null, "
                + statCategories[13] + " DECIMAL Default Null, "
                + statCategories[14] + " DECIMAL Default Null, "
                + statCategories[15] + " CHAR(25) Default Null, "
                + statCategories[16] + " DECIMAL Default Null, "
                + statCategories[18] + " DECIMAL Default Null, "
                + statCategories[19] + " DECIMAL Default Null, "
                + statCategories[20] + " CHAR(25) Default Null, "
                + statCategories[22] + " DECIMAL Default Null, "
                + statCategories[25] + " DECIMAL Default Null"
                + ")";
        stmt.execute(createTable2012);

        /*
         * Create the Stats2013 Table
         */
        String createTable2013 = "CREATE TABLE Stats2013("
                + statCategories[1] + " CHAR(25) Default Null, "
                + statCategories[2] + " DECIMAL Default Null, "
                + statCategories[3] + " DECIMAL Default Null, "
                + statCategories[4] + " CHAR(25) Default Null, "
                + statCategories[5] + " DECIMAL Default Null, "
                + statCategories[6] + " DECIMAL Default Null, "
                + statCategories[7] + " DECIMAL Default Null, "
                + statCategories[8] + " DECIMAL Default Null, "
                + statCategories[9] + " DECIMAL Default Null, "
                + statCategories[10] + " DECIMAL Default Null, "
                + statCategories[11] + " DECIMAL Default Null, "
                + statCategories[12] + " DECIMAL Default Null, "
                + statCategories[13] + " DECIMAL Default Null, "
                + statCategories[14] + " DECIMAL Default Null, "
                + statCategories[15] + " CHAR(25) Default Null, "
                + statCategories[16] + " DECIMAL Default Null, "
                + statCategories[18] + " DECIMAL Default Null, "
                + statCategories[19] + " DECIMAL Default Null, "
                + statCategories[20] + " CHAR(25) Default Null, "
                + statCategories[22] + " DECIMAL Default Null, "
                + statCategories[25] + " DECIMAL Default Null"
                + ")";
        stmt.execute(createTable2013);

        /*
         * Close the connection
         */
        conn.close();
    }
}
